set echo off 
BEGIN 

	DBMS_RANDOM.seed (val => TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF') );

	insert into user_info ( user_id, account, passwordhash ,addr_id,  addr_detail ) 
	values( user_id_seq.nextval, '13801231234','138138', 1234, '无名街');

	insert into user_info ( user_id, account, passwordhash ,addr_id,  addr_detail ) 
	select user_id_seq.nextval, '13901231234','139139', 2345, '有名坊' from dual;

	------------------  user 
	
	INSERT INTO user_log_hist ( USER_LOG_HIST_ID
									,USER_ID
									,login_dt 
									,LOGIN_STATUS
									)
	SELECT 	user_log_hist_id_seq.NEXTVAL
		   ,user_id 
		   ,sysdate - dt/42
		   ,decode ( mod( st, 2), 0, 'S', 'F')    --- fail 
	FROM user_info
	, (select  DBMS_RANDOM.value(low => 1, high => level * 30) dt
              ,	round( DBMS_RANDOM.value(low => 1, high => level + 1 )   , 0)  st
			  , round( DBMS_RANDOM.value(low => 1, high => level/2 + 1  ), 0)  ur
	   FROM DUAL CONNECT BY LEVEL < 100) r 		
	WHERE mod( ur, user_id ) = 0  ;

	DBMS_OUTPUT.PUT_LINE('Inserted into table user_log_hist, row count:'||sql%rowcount);
	

	INSERT INTO pond_info (  pond_id   
							,pond_name  
							,user_id 
							,area      
							,height    
							,prod      
							,density   
							,built_dt 
	) 
	select    pond_id_seq.nextval
			 ,'Fishing pond '||pn||' - user'||user_id 
			 , user_id  
			 , DBMS_RANDOM.value(low => 30, high => pn * 50)
			 , DBMS_RANDOM.value(low => 3, high => pn * 5)
			 , case when DBMS_RANDOM.value(low =>1, high =>10 ) > 5 then  'Fish' else 'Shrimp' end  
			 , DBMS_RANDOM.value(low => .01, high => 0.5)
			 , trunc(sysdate - DBMS_RANDOM.value(low => 100, high => 200))
	from ( 
		select distinct user_id, level pn 
			 from user_info  
		where user_id <  (select avg(user_id)+1  from user_info ) 
		connect by level <= ( select count(1) +1  from user_info)
		)
	;

	DBMS_OUTPUT.PUT_LINE('Inserted into table pond_info, row count:'||sql%rowcount);
	
	INSERT INTO device_list ( device_id
			 ,pond_id
			 ,device_sn 
						 )
	SELECT  rpad( 'TEST_DEV_'||pond_id||'_'||pn||'_'||round(DBMS_RANDOM.value(low => 1, high => 9) ,0),15, 'Y')
			,pond_id , device_list_sn_seq.nextval
	FROM ( select distinct pond_id, level pn
           from pond_info
			CONNECT BY LEVEL < (select count(1)+ 1  from user_info ) 
		);
	
	DBMS_OUTPUT.PUT_LINE('Inserted into table device_list, row count:'||sql%rowcount);
	
	INSERT INTO dev_outgoing_log (
						 dev_outgoing_logid  
						,device_id   
						,device_sn 
						,created_dt           
						,created_by           
						,data_type           
						,outgoing_data       
						,status              
		)
		SELECT  dev_outgoing_log_seq.NEXTVAL
			  ,device_id
			  ,device_sn 
			  ,SYSDATE - postpond_time
			  ,'Test_Data'
			  , data_type
			  ,outgoing_data
			  ,'N'		 
		FROM  ( SELECT DISTINCT device_id, device_sn , LEVEL PN FROM device_list CONNECT BY LEVEL < 5 ) 
		  ,   ( SELECT TO_CHAR( LEVEL, 'FM9') DATA_TYPE FROM DUAL CONNECT BY LEVEL < 7)
		  ,   ( SELECT DBMS_RANDOM.value(low => .01, high => .09) postpond_time,
						to_char(DBMS_RANDOM.value(low => 1, high => 999), 'fm999')||','||
						to_char(DBMS_RANDOM.value(low => 1, high => 999), 'fm999')||','||
						to_char(DBMS_RANDOM.value(low => 1, high => 999), 'fm999')||','||
						to_char(DBMS_RANDOM.value(low => 1, high => 999), 'fm999')||','||
						to_char(DBMS_RANDOM.value(low => 1, high => 999), 'fm999') outgoing_data
				from dual 
				 )
		WHERE pn > data_type and data_type <> '1'	
		;	

	DBMS_OUTPUT.PUT_LINE('Inserted into table dev_outgoing_log, row count:'||sql%rowcount);
	
	
	COMMIT;
END;
/				
set echo on 
